[Redshift] 関数を使用してユーザー情報の生年月日から現在の年齢、曜日を表示する

#データベース

takahara

2016.12.15

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

登録されたユーザーの生年月日から年齢と曜日を表示する方法について、Redshiftでどうすれば良いかを紹介します。

環境

MacOSX 10.10.5 Yosemite
Redshift 1.0.1125

準備

ユーザー情報テーブル：user_profile

CREATE TABLE IF EXISTS blog;

CREATE TABLE blog.user_profile (
  user_id INTEGER
  , user_name VARCHAR(50)
  , birth DATE
  , sex SMALLINT
  , PRIMARY KEY(user_id)
);
INSERT INTO blog.user_profile VALUES
  (1001,'Abiel','1980-01-01',0)
 ,(1002,'Salome','1990-03-05',0)
 ,(1003,'Cornelius','2000-12-31',1)
 ,(1004,'Joseph','1958-08-29',1)
 ,(1005,'Drusilla','1997-12-13',0);

# select * from blog.user_profile;
 user_id | user_name |   birth    | sex 
---------+-----------+------------+-----
    1001 | Abiel     | 1980-01-01 |   0
    1002 | Salome    | 1990-03-05 |   0
    1003 | Cornelius | 2000-12-31 |   1
    1004 | Joseph    | 1958-08-29 |   1
    1005 | Drusilla  | 1997-12-13 |   0
(5 rows)

左から、ユーザーID、ユーザー名、生年月日、性別。

使用する関数について

DATEDIFF

指定した年月日時と年月日時の期間を知る事ができます。
Amazon Redshift | DATEDIFF 関数

SQL使用例

SELECT
  birth
  , getdate()
  , DATEDIFF(year,     birth, getdate()) AS year
  , DATEDIFF(month,    birth, getdate()) AS month
  , DATEDIFF(week,     birth, getdate()) AS week
  , DATEDIFF(day,      birth, getdate()) AS day
  , DATEDIFF(hour,     birth, getdate()) AS hour
  , DATEDIFF(minute,   birth, getdate()) AS minute
  , DATEDIFF(second,   birth, getdate()) AS second
  , DATEDIFF(millisec, birth, getdate()) AS millisecond
  , DATEDIFF(microsec, birth, getdate()) AS microsecond
FROM
  blog.user_profile
ORDER BY
  user_id;

第１引数に何を抽出したいか指定するだけです。
上記ではgetdate()でUTC現在日時を指定していますね。

実行結果

.
   birth    |       getdate       | year | month | week |  day  |  hour  |  minute  |   second   |  millisecond  |   microsecond    
------------+---------------------+------+-------+------+-------+--------+----------+------------+---------------+------------------
 1980-01-01 | 2016-12-14 14:39:52 |   36 |   443 | 1928 | 13497 | 323942 | 19436559 | 1166193592 | 1166193592000 | 1166193592000000
 1990-03-05 | 2016-12-14 14:39:52 |   26 |   321 | 1397 |  9781 | 234758 | 14085519 |  845131192 |  845131192000 |  845131192000000
 2000-12-31 | 2016-12-14 14:39:52 |   16 |   192 |  832 |  5827 | 139862 |  8391759 |  503505592 |  503505592000 |  503505592000000
 1958-08-29 | 2016-12-14 14:39:52 |   58 |   700 | 3042 | 21292 | 511022 | 30661359 | 1839681592 | 1839681592000 | 1839681592000000
 1997-12-13 | 2016-12-14 14:39:52 |   19 |   228 |  992 |  6941 | 166598 |  9995919 |  599755192 |  599755192000 |  599755192000000
(5 rows)

EXTRACT

こちらも引数に指定して抽出します。
Amazon Redshift | EXTRACT 関数

SQL使用例

SELECT
  birth
  , EXTRACT(year FROM birth) AS year
  , EXTRACT(dow FROM birth) AS days_of_week
FROM
  blog.user_profile
ORDER BY
  user_id;

上記の様に「dow」などと指定すると曜日を数値で取得できます。
0〜6の数が日曜〜土曜までを表します。

実行結果

.
   birth    | year | days_of_week 
------------+------+--------------
 1980-01-01 | 1980 |            2
 1990-03-05 | 1990 |            1
 2000-12-31 | 2000 |            0
 1958-08-29 | 1958 |            5
 1997-12-13 | 1997 |            6
(5 rows)

現在の年齢と生年月日の曜日を作成

以上を踏まえて年齢と曜日を作成してみたいと思います。

SQL

SELECT
  user_id
  , user_name
  , birth
  , T.today

  -- 曜日番号から曜日を作成。
  , CASE EXTRACT(dow FROM birth)
         WHEN 0 THEN 'sun'
         WHEN 1 THEN 'mon'
         WHEN 2 THEN 'tue'
         WHEN 3 THEN 'wed'
         WHEN 4 THEN 'thu'
         WHEN 5 THEN 'fri'
         WHEN 6 THEN 'sat'
		 END dow

  -- 期間（年）
  , DATEDIFF(YEAR, birth, T.today) AS year_diff

  -- 年齢
    -- 「生年月日の年」と「今日の年」の「年数の差」を作成。
  , DATEDIFF(YEAR, birth, T.today) +
    -- 今年の誕生日と今日を比較して、誕生日が来ていなければ上記の「年数の差」から1を引く。
    (CASE EXTRACT(doy FROM TO_DATE(TO_CHAR(T.today,'YYYY')||TO_CHAR(birth,'MMDD'),'YYYYMMDD')) > EXTRACT(doy FROM T.today)
          WHEN TRUE  THEN -1 -- 「今年の誕生日 >  今日」なので、まだ誕生日が来ていないので「年数の差」に-1する。
          WHEN FALSE THEN 0  -- 「今年の誕生日 =< 今日」なので、既に誕生日が来ているので「年数の差」はそのまま。
          END) AS age
FROM
  blog.user_profile
    -- JSTの日時を作成してクロスジョインする。
    CROSS JOIN (SELECT TRUNC( CONVERT_TIMEZONE('JST',GETDATE()) ) AS today) AS T
ORDER BY
  user_id;

曜日は、関数EXTRACTで取得した数値をCASEで分岐させて曜日の文字列に置換しています。
年齢は、DATEDIFFで年数の差を作成していますが、あくまでも年数の差ですので、SQLでも解説している様に、誕生日が来ていなければ1を引く必要があります。

実行結果

.
 user_id | user_name |   birth    |   today    | dow | year_diff | age 
---------+-----------+------------+------------+-----+-----------+-----
    1001 | Abiel     | 1981-03-01 | 2016-12-18 | sun |        35 |  35
    1002 | Salome    | 2000-02-29 | 2016-12-18 | tue |        16 |  16
    1003 | Cornelius | 2000-12-31 | 2016-12-18 | sun |        16 |  15
    1004 | Joseph    | 1958-08-29 | 2016-12-18 | fri |        58 |  58
    1005 | Drusilla  | 2000-12-13 | 2016-12-18 | wed |        16 |  16
(5 rows)

「年数の差」だけのカラムyear_diffとageを比べると、誕生日が来ていないuser_id=1003は-1されました。